package edu.hawaii.myisern.derby;

import java.io.File;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.bind.JAXBContext;
import javax.xml.bind.Unmarshaller;
import edu.hawaii.myisern.collaborations.jaxb.Collaboration;
import edu.hawaii.myisern.collaborations.jaxb.Collaborations;
import edu.hawaii.myisern.exceptions.InvalidCollaborationNameException;
import edu.hawaii.myisern.exceptions.InvalidOrganizationNameException;
import edu.hawaii.myisern.exceptions.InvalidResearcherNameException;
import edu.hawaii.myisern.organizations.jaxb.Organization;
import edu.hawaii.myisern.organizations.jaxb.Organizations;
import edu.hawaii.myisern.researchers.jaxb.Researcher;
import edu.hawaii.myisern.researchers.jaxb.Researchers;


/**
 * Derby wrapper class for myIsern. Inspired by
 * http://db.apache.org/derby/integrate/plugin_help/derby_app.html.
 * 
 * @author John Hauge
 * @author Marcius Bagwan
 */
public class DerbyWrapper {
  /** The JDBC driver. */
  private String driver = "org.apache.derby.jdbc.EmbeddedDriver";
  /** The key for putting/retrieving the directory where Derby will create its databases. */
  private static final String derbySystemKey = "derby.system.home";
  private String dbName = "myiserndb";
  private String dbURL = "jdbc:derby:" + dbName + ";create=true;";

  private static Connection conn = null;
  private static Statement stmt = null;

  private static Logger logger = Logger.getLogger("edu.hawaii.myisern.onyx");

  private String r_fname = "r_fname";
  private String r_mname = "r_mname";
  private String r_lname = "r_lname";
  private String tbl = "FROM tbl_ORGANIZATION ";
  
  /**
   * Constructor.
   */
  public DerbyWrapper() {
    try {
      // Make sure the directory exists, creating it if not present.
      File myisernHome = new File(System.getProperty("user.home"), ".myiserndb");
      File dbHome = new File(myisernHome, "db");
      dbHome.mkdirs();
      // Set the system property used by Derby to locate the db files.
      System.getProperties().put(this.derbySystemKey, dbHome.getAbsolutePath());
      // Now load the Derby driver.
      createConnection();
    }
    catch (Exception e) {
      this.logger.log(Level.SEVERE, e.toString());
    }

  }

  /**
   * Overloaded Constructor.
   * 
   * @param dbName Name of database to connect to.
   */
  public DerbyWrapper(String dbName) {
    try {
      this.dbName = dbName;
      this.dbURL = "jdbc:derby:" + dbName + ";create=true;";
      // Make sure the directory exists, creating it if not present.
      File myisernHome = new File(System.getProperty("user.home"), ".myiserndb");
      File dbHome = new File(myisernHome, "db");
      dbHome.mkdirs();
      // Set the system property used by Derby to locate the db files.
      System.getProperties().put(derbySystemKey, dbHome.getAbsolutePath());
      // Now load the Derby driver.
      createConnection();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }

  }

  /**
   * Cleanup.
   */
  public void dispose() {
    closeConnection();
  }

  /**
   * Creates the connection to the database which allows SQL statements to be executed.
   */
  private void createConnection() {
    try {
      Class.forName(driver).newInstance();
      // Get a connection
      this.conn = DriverManager.getConnection(dbURL);
    }
    catch (Exception e) {
      this.logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Closes the connection to the database.
   */
  private void closeConnection() {
    try {
      if (stmt != null) {
        stmt.close();
      }
      if (conn != null) {
        // DriverManager.getConnection(dbURL + ";shutdown=true");
        conn.close();
        conn = null;
      }
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Method for importing xml files into the database.
   * 
   * @param importType specifies the type of file importing.
   * @param filePath path to file.
   * @return a number.
   */
  public int xmlImporter(String importType, String filePath) {
    try {
      /** Holds the class-wide JAXBContext, which is thread-safe. */
      JAXBContext jaxbContext;
      Collaborations unmarshalledCollab = null;
      Organizations unmarshalledOrg = null;
      Researchers unmarshalledResearchers = null;
      String jaxbContentString = "edu.hawaii.myisern." + importType + ".jaxb";
      jaxbContext = JAXBContext.newInstance(jaxbContentString);
      File importFile = new File(filePath);
      Unmarshaller unmarshaller = jaxbContext.createUnmarshaller();
      if ("collaborations".equals(importType)) {
        unmarshalledCollab = (Collaborations) unmarshaller.unmarshal(importFile);
        for (Collaboration collab : unmarshalledCollab.getCollaboration()) {
          addCollaboration(collab);
        }
      }
      else if ("organizations".equals(importType)) {
        unmarshalledOrg = (Organizations) unmarshaller.unmarshal(importFile);
        for (Organization org : unmarshalledOrg.getOrganization()) {
          addOrganization(org);
        }
      }
      else if ("researchers".equals(importType)) {
        unmarshalledResearchers = (Researchers) unmarshaller.unmarshal(importFile);
        for (Researcher researcher : unmarshalledResearchers.getResearcher()) {
          addResearcher(researcher);
        }
      }
      // additional operations
      if ("collaborations".equals(importType)) {
        int i = 0;
        
        /*
        for (Collaboration collab : unmarshalledCollab.getCollaboration()) {
        for(String orgName : 
                collab.getCollaboratingOrganizations().getCollaboratingOrganization()) {
          int c_id = getCollaborationId(collab.getName());
          int o_id = getOrganizationId(orgName);
          if (c_id == -1) {
            throw new InvalidCollaborationNameException();
          }
          else if (o_id == -1){
            throw new InvalidOrganizationNameException();
          }
          else {
            addCollaboratingOrganization(c_id, o_id);
          }
        }
      }*/
      }
      else if ("researchers".equals(importType)) {
        for (Researcher researcher : unmarshalledResearchers.getResearcher()) {
          int r_id = getResearcherId(researcher.getName());
          int o_id = getOrganizationId(researcher.getOrganization());
          // System.out.println("o(" + o_id + "): " + researcher.getOrganization());
          if (r_id == -1) {
            throw new InvalidCollaborationNameException(researcher.getName());
          }
          else if (o_id == -1) {
            throw new InvalidOrganizationNameException(researcher.getOrganization());
          }
          else {
            logger.log(Level.INFO, "AddingResearcherOrganization(" + r_id + ", " + o_id + ")");
            addResearcherOrganization(r_id, o_id);
          }
        }
      }
      else if ("organizations".equals(importType)) {
        for (Organization org : unmarshalledOrg.getOrganization()) {
          for (String r_name : org.getAffiliatedResearchers().getAffiliatedResearcher()) {
            if (r_name.length() > 0) {
              int r_id = getResearcherId(r_name);
              int o_id = getOrganizationId(org.getName());
              if (r_id == -1) {
                // System.out.println("invalid r_name: " + r_name + " --" + r_name.length());
                throw new InvalidResearcherNameException(r_name);
              }
              else if (o_id == -1) {
                throw new InvalidOrganizationNameException(org.getName());
                // System.out.println("invalid o_name: " + org.getName());
              }
              else {
                logger.log(Level.INFO, "AddingResearcherOrganization(" + r_id + ", " + o_id + ")");
                addResearcherOrganization(r_id, o_id);
              }
            }
          }
        }
      }

      return 0;
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
      return -1;
    }
  }

  /**
   * Method for exporting xml files from the database.
   * 
   * @param exportType specifies the type of file exporting.
   * @param filePath path to file.
   * @return a number.
   */
  public int xmlExport(String exportType, String filePath) {

    return 0;
  }

  /**
   * Creates all tables.
   */
  public void createAllTables() {
    try {
      // Tables
      // Organization_Type
      createOrganizationTypeTable();
      // Country
      createCountryTable();
      // Researcher
      createResearcherTable();
      // Organization
      createOrganizationTable();
      // Reseracher_Organization
      createResearcherOrganizationTable();
      // Research_Keywords
      createResearchKeywordTable();
      // Collaboration
      createCollaborationTable();
      // Collaborating_Organizations
      createCollaboratingOrganizationTable();
      // Outcome_Type
      createOutcomeTypeTable();
      // Years
      createYearTable();
      // Collaboration_Type
      createCollaborationTypeTable();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Drops all tables.
   */
  public void dropAllTables() {
    try {
      // Tables
      // Collaborating_Organizations
      dropCollaboratingOrganizationTable();
      // Research_Keywords
      dropResearchKeywordTable();
      // Reseracher_Organization
      dropResearcherOrganizationTable();
      // Organization
      dropOrganizationTable();
      // Organization_Type
      dropOrganizationTypeTable();
      // Country
      dropCountryTable();
      // Researcher
      dropResearcherTable();
      // Outcome_Type
      dropOutcomeTypeTable();
      // Years
      dropYearTable();
      // Collaboration_Type
      dropCollaborationTypeTable();
      // Collaboration
      dropCollaborationTable();

    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Creates the tbl_RESEARCHER Table.
   */
  public void createResearcherTable() {
    try {
      String sqlCmd = "CREATE TABLE tbl_RESEARCHER ("
          + "   r_id      INT           GENERATED ALWAYS AS IDENTITY"
          + "                           CONSTRAINT r_id_pk PRIMARY KEY,"
          + "   r_fname   VARCHAR(64)   NOT NULL," + "   r_mname   VARCHAR(64)   ,"
          + "   r_lname   VARCHAR(64)   NOT NULL," + "   r_email   VARCHAR(128)  ,"
          + "   r_picLink VARCHAR(256)  ," + "   r_bio     VARCHAR(1024) " + ")";
      stmt = conn.createStatement();
      stmt.execute(sqlCmd);
      stmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Drops the tbl_RESEARCHER Table.
   */
  public void dropResearcherTable() {
    try {
      String sqlCmd = "DROP TABLE tbl_RESEARCHER";
      stmt = conn.createStatement();
      stmt.execute(sqlCmd);
      stmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Adds a researcher to the table "tbl_RESEARCHER" in the database.
   * 
   * @param r Researcher to add.
   */
  public void addResearcher(Researcher r) {
    try {
      String[] names = splitUpName(r.getName());
      String sqlCmd = "INSERT INTO tbl_RESEARCHER "
          + "(r_fname, r_mname, r_lname, r_email, r_picLink, r_bio)" + "VALUES (?,?,?,?,?,?)";
      PreparedStatement pStmt = conn.prepareStatement(sqlCmd);
      pStmt.setString(1, names[0]);
      pStmt.setString(2, names[1]);
      pStmt.setString(3, names[2]);
      pStmt.setString(4, r.getEmail());
      pStmt.setString(5, r.getPictureLink());
      pStmt.setString(6, r.getBioStatement());
      pStmt.execute();
      pStmt.close();

      sqlCmd = "INSERT INTO tbl_RESEARCHER_ORGANIZATION" + "(r_id, o_id) VALUES (?,?)";
      // TODO Support multiple orgs when [Issue 2] taken care of
      pStmt = conn.prepareStatement(sqlCmd);
      int id = getResearcherId(r.getName());
      pStmt.setInt(1, id);
      id = getOrganizationId(r.getOrganization());
      if (id == -1) {
        throw new InvalidOrganizationNameException(r.getOrganization());
      }
      else {
        pStmt.setInt(2, id);
        pStmt.execute();
      }
      pStmt.close();

    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Get a researcher provided the database ID.
   * 
   * @param id of the researcher.
   * @return a researcher object.
   * @throws Exception if error occurs.
   */
  public Researcher getResearcher(int id) throws Exception {
    PreparedStatement pStmt = null;
    Researcher r = new Researcher();
    ResultSet results = null;
    try {
      String sqlCmd = "SELECT r_fname, r_mname, r_lname, r_email, r_picLink, r_bio "
          + "FROM tbl_RESEARCHER WHERE r_id = ?";
      // System.out.println("sqlCmd: " + sqlCmd);
      pStmt = conn.prepareStatement(sqlCmd);
      pStmt.setInt(1, id);
      results = pStmt.executeQuery();
      results.next();
      // System.out.println("(id: " + id + ")\tResults: " + results.toString());
      // System.out.println("Results fname: " + results.getString(r_fname));

      r.setName(results.getString(r_fname) + " " + results.getString(r_mname) + " "
          + results.getString(r_lname));
      r.setEmail(results.getString("r_email"));
      r.setPictureLink(results.getString("r_picLink"));
      r.setBioStatement(results.getString("r_bio"));
      // Still need to add in code for multiple organizations

    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
      r = null;
    }
    finally {
      results.close();
      pStmt.close();
    }
    return r;
  }

  /**
   * Gets the database ID of an researcher given their name from the "Researcher" table.
   * 
   * @param name of the researcher.
   * @return the id number of the researcher.
   * @throws Exception if error occurs.
   */
  public int getResearcherId(String name) throws Exception {
    int id = -1;
    PreparedStatement pStmt = null;
    ResultSet results = null;
    try {
      // System.out.println("Name: " + name);
      String[] names = splitUpName(name);
      /*
       * System.out.println("fname: " + names[0]); System.out.println("mname: " + names[1]);
       * System.out.println("lname: " + names[2]);
       */
      String sqlCmd = "SELECT r_id " + "FROM tbl_RESEARCHER " + "WHERE r_fname = ? "
          + "AND r_mname = ? " + "AND r_lname = ? ";
      pStmt = conn.prepareStatement(sqlCmd);
      pStmt.setString(1, names[0]);
      pStmt.setString(2, names[1]);
      pStmt.setString(3, names[2]);
      results = pStmt.executeQuery();
      if (results.next()) {
        id = results.getInt("r_id");
      }
      else {
        id = -1;
      }
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
    finally {
      results.close();
      pStmt.close();
    }
    return id;
  }

  /**
   * Gets a list of all researchers in the tbl_RESEARCHER table
   * 
   * @return an array list of researcher objects.
   * @throws Exception if error occurs.
   */
  public ArrayList<Researcher> getResearcherList() throws Exception {
    ArrayList<Researcher> list = new ArrayList<Researcher>();
    Researcher r;
    ResultSet results = null;
    try {
      String sqlCmd = "SELECT r_fname, r_mname, r_lname, r_email, r_picLink, r_bio "
          + "FROM tbl_RESEARCHER ";
      stmt = conn.createStatement();
      results = stmt.executeQuery(sqlCmd);
      while (results.next()) {
        r = new Researcher();

        r.setName(results.getString(this.r_fname) + " " + results.getString(this.r_mname) + " "
            + results.getString(this.r_lname));
        r.setEmail(results.getString("r_email"));
        r.setPictureLink(results.getString("r_picLink"));
        r.setBioStatement(results.getString("r_bio"));
        list.add(r);
      }
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
      list = null;
    }
    finally {
      results.close();
      stmt.close();
    }
    return list;

  }

  /**
   * Gets a list of the names of all researchers in the tbl_RESEARCHER table.
   * 
   * @return List of researcher names.
   * @throws SQLException If error occurs.
   */
  public List<String> getResearcherNameList() throws SQLException {
    ArrayList<String> list = new ArrayList<String>();
    PreparedStatement pStmt = null;
    ResultSet results = null;
    try {
      String sqlCmd = "SELECT r_fname, r_mname, r_lname " + "FROM tbl_RESEARCHER ";
      pStmt = conn.prepareStatement(sqlCmd);
      results = pStmt.executeQuery();
      while (results.next()) {
        list.add(getFullName(results.getString(r_fname), results.getString(r_mname), results
            .getString(r_lname)));
      }
      return list;
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
      return null;
    }
    finally {
      pStmt.close();
      results.close();
    }
  }

  /**
   * Creates the tbl_ORGANIZATION Table.
   */
  public void createOrganizationTable() {
    try {
      String sqlCmd = "CREATE TABLE tbl_ORGANIZATION ("
          + "   o_id          INT           GENERATED ALWAYS AS IDENTITY"
          + "                               CONSTRAINT o_id_pk PRIMARY KEY,"
          + "   o_name        VARCHAR(64)   NOT NULL"
          + "                               CONSTRAINT o_name_uc UNIQUE,"
          + "   o_type_id     INT           CONSTRAINT o_type_id_fk"
          + "                               REFERENCES tbl_ORGANIZATION_TYPE,"
          + "   o_contact_id  INT           CONSTRAINT o_contact_id_fk"
          + "                               REFERENCES tbl_RESEARCHER,"
          + "   o_country_id  INT           CONSTRAINT o_country_id_fk"
          + "                               REFERENCES tbl_COUNTRY,"
          + "   o_desc        VARCHAR(1028), " + "   o_homepage    VARCHAR(256) " + ")";
      stmt = conn.createStatement();
      stmt.execute(sqlCmd);
      stmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Drops the tbl_ORGANIZATION Table.
   */
  public void dropOrganizationTable() {
    try {
      String sqlCmd = " DROP TABLE tbl_ORGANIZATION";
      stmt = conn.createStatement();
      stmt.execute(sqlCmd);
      stmt.close();

    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Adds an organization to the table "tbl_ORGANIZATION" in the database.
   * 
   * @param o Organization to add.
   */
  public void addOrganization(Organization o) {
    try {
      String sqlCmd = "INSERT INTO tbl_ORGANIZATION "
          + "(o_name, o_type_id, o_contact_id, o_country_id, o_desc, o_homepage)"
          + "VALUES (?,?,?,?,?,?)";
      PreparedStatement pStmt = conn.prepareStatement(sqlCmd);
      pStmt.setString(1, o.getName().trim());
      int otype_id = getOrganizationTypeId(o.getType().trim());
      if (otype_id == -1) {
        addOrganizationType(o.getType());
        otype_id = getOrganizationTypeId(o.getType().trim());
      }
      pStmt.setInt(2, otype_id);

      int r_id = getResearcherId(o.getContact().trim());
      if (r_id == -1) {
        pStmt.setNull(3, Types.INTEGER);
      }
      else {
        pStmt.setInt(3, r_id);
      }

      int country_id = getCountryId(o.getCountry().trim());
      if (country_id == -1) {
        addCountry(o.getCountry());
        country_id = getCountryId(o.getCountry().trim());
      }
      pStmt.setInt(4, country_id);
      pStmt.setString(5, o.getResearchDescription().trim());
      pStmt.setString(6, o.getHomePage().trim());
      pStmt.execute();
      pStmt.close();

      sqlCmd = "INSERT INTO tbl_RESEARCH_KEYWORD " + "(o_id, r_keyword) VALUES (?,?)";
      pStmt = conn.prepareStatement(sqlCmd);
      int o_id = getOrganizationId(o.getName().trim());
      if (o_id == -1) {
        throw new InvalidOrganizationNameException(o.getName());
      }
      else {
        for (String keyword : o.getResearchKeywords().getResearchKeyword()) {
          pStmt.setInt(1, o_id);
          pStmt.setString(2, keyword.trim());
          pStmt.execute();
        }
      }

      sqlCmd = "INSERT INTO tbl_RESEARCHER_ORGANIZATION " + "(r_id, o_id) VALUES (?,?)";
      pStmt = conn.prepareStatement(sqlCmd);
      for (String researcher : o.getAffiliatedResearchers().getAffiliatedResearcher()) {
        r_id = getResearcherId(researcher);
        if (r_id == -1) {
          throw new InvalidResearcherNameException(researcher);
        }
        else {
          pStmt.setInt(1, r_id);
          pStmt.setInt(2, o_id);
          pStmt.execute();
        }
      }
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Get a organization provided the database ID.
   * 
   * @param id of the researcher.
   * @return a researcher object.
   * @throws Exception if error occurs.
   */
  public Organization getOrganization(int id) throws Exception {
    PreparedStatement pStmt = null;
    Organization o = new Organization();
    ResultSet results = null;
    try {
      String sqlCmd = "SELECT o_name, otype_name, r_fname, r_mname, r_lname, " +
      		            "country_name, o_desc, o_homepage " + 
      		            tbl + 
                      "LEFT OUTER JOIN tbl_RESEARCHER ON o_contact_id = r_id " +
                      "LEFT OUTER JOIN tbl_ORGANIZATION_TYPE ON o_type_id = otype_id " +
                      "LEFT OUTER JOIN tbl_COUNTRY ON o_country_id = country_id " + 
                      "WHERE o_id = ?";
      //System.out.println("sqlCmd: " + sqlCmd);
      pStmt = conn.prepareStatement(sqlCmd);
      pStmt.setInt(1, id);
      results = pStmt.executeQuery();
      results.next();
      // System.out.println("(id: " + id + ")\tResults: " + results.toString());
      // System.out.println("Results fname: " + results.getString(r_fname));

      o.setName(results.getString("o_name"));
      o.setType(results.getString("otype_name"));
      o.setContact(results.getString(r_fname) + " " + results.getString(r_mname) + " "
          + results.getString(r_lname));
      o.setCountry(results.getString("country_name"));
      o.setResearchDescription(results.getString("o_desc"));
      o.setHomePage(results.getString("o_homepage"));

      // Still need to add in code for multiple organizations

    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
      o = null;
    }
    finally {
      results.close();
      pStmt.close();
    }
    return o;
  }

  /**
   * Gets the database ID of an organization given their name from the "tbl_ORGANIZATION" table.
   * 
   * @param name of the organization.
   * @return the id number of the organization.
   * @throws Exception if error occurs.
   */
  public int getOrganizationId(String name) throws Exception {
    int id = -1;
    PreparedStatement pStmt = null;
    ResultSet results = null;
    // System.out.println("\torgName: " + name);
    try {
      String sqlCmd = "SELECT o_id " + 
                      tbl +  
                      "WHERE o_name = ?";
      pStmt = conn.prepareStatement(sqlCmd);
      pStmt.setString(1, name);
      results = pStmt.executeQuery();
      if (results.next()) {
        id = results.getInt("o_id");
      }
      else {
        id = -1;
      }
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
    finally {
      results.close();
      pStmt.close();
    }
    return id;
  }

  /**
   * Gets a list of all organizations in the tbl_ORGANIZATION table
   * 
   * @return an array list of organization objects.
   * @throws Exception if error occurs.
   */
  public ArrayList<Organization> getOrganizationList() throws Exception {
    ArrayList<Organization> list = new ArrayList<Organization>();
    Organization o;
    ResultSet o_results = null;
    // ResultSet r_results = null;
    try {
      String sqlCmd = "SELECT o_id, o_name, otype_name, r_fname, r_mname, r_lname, " +
                      "country_name, o_desc, o_homepage " +
                      tbl + 
                      "LEFT OUTER JOIN tbl_RESEARCHER ON o_contact_id = r_id " +
                      "LEFT OUTER JOIN tbl_ORGANIZATION_TYPE ON o_type_id = otype_id " +
                      "LEFT OUTER JOIN tbl_COUNTRY ON o_country_id = country_id";
      stmt = conn.createStatement();
      o_results = stmt.executeQuery(sqlCmd);
      while (o_results.next()) {
        o = new Organization();

        o.setName(o_results.getString("o_name"));
        o.setType(o_results.getString("otype_name"));
        o.setContact(getFullName(o_results.getString("r_fname"), o_results.getString("r_mname"),
            o_results.getString("r_lname")));
        o.setCountry(o_results.getString("country_name"));
        o.setResearchDescription(o_results.getString("o_desc"));
        o.setHomePage(o_results.getString("o_homepage"));
        // TODO getResearcherOrganizationList()
        // TODO getResearcherOrganizationList(String type, int id)
        // TODO getResearchKeywordList(int orgId)
        list.add(o);
      }
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
      list = null;
    }
    finally {
      o_results.close();
      stmt.close();
    }
    return list;

  }

  /**
   * Gets a list of the names of all organizations in the tbl_ORGANIZATION table.
   * 
   * @return List of organization names.
   * @throws SQLException If error occurs.
   */
  public List<String> getOrganizationNameList() throws SQLException {
    ArrayList<String> list = new ArrayList<String>();
    PreparedStatement pStmt = null;
    ResultSet results = null;
    try {
      String sqlCmd = "SELECT o_name " + 
                      tbl;  
      pStmt = conn.prepareStatement(sqlCmd);
      results = pStmt.executeQuery();
      while (results.next()) {
        list.add(results.getString("o_name"));
      }
      return list;
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
      return null;
    }
    finally {
      pStmt.close();
      results.close();
    }
  }

  /**
   * Creates the tbl_ORGANIZATION_TYPE table.
   */
  public void createOrganizationTypeTable() {
    try {
      String sqlCmd = "CREATE TABLE tbl_ORGANIZATION_TYPE ("
          + "   otype_id          INT           GENERATED ALWAYS AS IDENTITY"
          + "                                   CONSTRAINT otype_id_pk PRIMARY KEY,"
          + "   otype_name        VARCHAR(64)   NOT NULL" + ")";
      stmt = conn.createStatement();
      stmt.execute(sqlCmd);
      stmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Drops the tbl_ORGANIZATION_TYPE Table.
   */
  public void dropOrganizationTypeTable() {
    try {
      String sqlCmd = "DROP TABLE tbl_ORGANIZATION_TYPE";
      stmt = conn.createStatement();
      stmt.execute(sqlCmd);
      stmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Adds an organization type to the table "tbl_ORGANIZATION_TYPE" in the database.
   * 
   * @param type organization type to add.
   */
  public void addOrganizationType(String type) {
    try {
      String sqlCmd = "INSERT INTO tbl_ORGANIZATION_TYPE " + "(otype_name)" + "VALUES (?)";
      PreparedStatement pStmt = conn.prepareStatement(sqlCmd);
      pStmt.setString(1, type.trim());
      pStmt.execute();
      pStmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Gets the database ID of an organization type given the type name from the
   * "tbl_ORGANIZATION_TYPE" table.
   * 
   * @param type Type name.
   * @return a number.
   * @throws Exception if error occurs.
   */
  public int getOrganizationTypeId(String type) throws Exception {
    int id = -1;
    PreparedStatement pStmt = null;
    ResultSet results = null;
    try {
      String sqlCmd = "SELECT otype_id " + "FROM tbl_ORGANIZATION_TYPE " + "WHERE otype_name = ?";
      pStmt = conn.prepareStatement(sqlCmd);
      pStmt.setString(1, type);
      results = pStmt.executeQuery();
      if (results.next()) {
        id = results.getInt("otype_id");
      }
      else {
        id = -1;
      }
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
    finally {
      results.close();
      pStmt.close();
    }
    return id;
  }

  /**
   * Creates the tbl_RESEARCH_KEYWORD Table.
   */
  public void createResearchKeywordTable() {
    try {
      String sqlCmd = "CREATE TABLE tbl_RESEARCH_KEYWORD ("
          + "   o_id          INT           NOT NULL"
          + "                               CONSTRAINT o_id_keyword_fk"
          + "                               REFERENCES tbl_ORGANIZATION,"
          + "   r_keyword     VARCHAR(128)  NOT NULL," +

          "   CONSTRAINT o_id_k_pk        PRIMARY KEY(o_id, r_keyword)" + ")";
      stmt = conn.createStatement();
      stmt.execute(sqlCmd);
      stmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Drops the tbl_RESEARCH_KEYWORD Table.
   */
  public void dropResearchKeywordTable() {
    try {
      String sqlCmd = " DROP TABLE tbl_RESEARCH_KEYWORD";
      stmt = conn.createStatement();
      stmt.execute(sqlCmd);
      stmt.close();

    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Adds a research keyword to the tbl_RESEARCH_KEYWORD table.
   * 
   * @param orgId Id number of the organization
   * @param keyword Keyword to add.
   * @throws SQLException If error occurs.
   */
  public void addResearchKeyword(int orgId, String keyword) throws SQLException {
    PreparedStatement pStmt = null;
    try {
      String sqlCmd = "INSERT INTO tbl_RESEARCH_KEYWORD " + "(o_id, r_keyword)" + "VALUES (?,?)";
      pStmt = conn.prepareStatement(sqlCmd);
      pStmt.setInt(1, orgId);
      pStmt.setString(2, keyword.trim());
      pStmt.execute();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
    finally {
      pStmt.close();
    }
  }

  /**
   * Creates the tbl_RESEARCHER_ORGANIZATION Table.
   */
  public void createResearcherOrganizationTable() {
    try {
      String sqlCmd = "CREATE TABLE tbl_RESEARCHER_ORGANIZATION ("
          + "   r_id          INT           NOT NULL"
          + "                               CONSTRAINT o_id_fk"
          + "                               REFERENCES tbl_ORGANIZATION,"
          + "   o_id          INT           NOT NULL"
          + "                               CONSTRAINT r_id_fk"
          + "                               REFERENCES tbl_RESEARCHER," +

          "   CONSTRAINT r_o_id_pk        PRIMARY KEY(r_id, o_id)" + ")";
      stmt = conn.createStatement();
      stmt.execute(sqlCmd);
      stmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Drops the tbl_RESEARCHER_ORGANIZATION Table.
   */
  public void dropResearcherOrganizationTable() {
    try {
      String sqlCmd = "DROP TABLE tbl_RESEARCHER_ORGANIZATION";
      stmt = conn.createStatement();
      stmt.execute(sqlCmd);
      stmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Adds an entity to the tbl_RESEARCHER_ORGANIZATION Table.
   * 
   * @param rId Researcher Id number.
   * @param oId Organization Id number.
   */
  public void addResearcherOrganization(int rId, int oId) {
    try {
      String sqlCmd = "INSERT INTO tbl_RESEARCHER_ORGANIZATION " + "(r_id, o_id) " + "VALUES (?,?)";
      PreparedStatement pStmt = conn.prepareStatement(sqlCmd);
      pStmt.setInt(1, rId);
      pStmt.setInt(2, oId);
      pStmt.execute();
      pStmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Gets a from the tbl_RESEARCHER_ORGANIZATION table.
   * 
   * @return map<Researcher, Organization>
   * @throws SQLException If error occurs.
   */
  public Map<Researcher, Organization> getResearcherOrganizationList() throws SQLException {
    Map<Researcher, Organization> map = new HashMap<Researcher, Organization>();
    ResultSet results = null;
    try {
      String sqlCmd = "SELECT r_id, o_id " + "FROM tbl_RESEARCHER_ORGANIZATION ";
      stmt = conn.createStatement();
      results = stmt.executeQuery(sqlCmd);
      while (results.next()) {
        int r_id = results.getInt("r_id");
        int o_id = results.getInt("o_id");
        Researcher r = getResearcher(r_id);
        Organization o = getOrganization(o_id);
        map.put(r, o);
      }
      return map;
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
      return null;
    }
    finally {
      stmt.close();
      results.close();
    }
  }

  /**
   * Creates the tbl_COLLABORATION Table.
   */
  public void createCollaborationTable() {
    try {
      String sqlCmd = "CREATE TABLE tbl_COLLABORATION ("
          + "   c_id          INT           GENERATED ALWAYS AS IDENTITY"
          + "                               CONSTRAINT c_id_pk PRIMARY KEY,"
          + "   c_name        VARCHAR(128)   NOT NULL," + "   c_desc        VARCHAR(1028)  " + ")";
      stmt = conn.createStatement();
      stmt.execute(sqlCmd);
      stmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Drops the tbl_COLLABORATION Table.
   */
  public void dropCollaborationTable() {
    try {
      String sqlCmd = " DROP TABLE tbl_COLLABORATION";
      stmt = conn.createStatement();
      stmt.execute(sqlCmd);
      stmt.close();

    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Adds a collaboration to the table "tbl_COLLABORATION" in the database.
   * 
   * @param c Collaboration to add.
   */
  @SuppressWarnings("deprecation")
  public void addCollaboration(Collaboration c) {
    try {
      int c_id = -1;
      String sqlCmd = "INSERT INTO tbl_COLLABORATION " + "(c_name, c_desc) VALUES (?,?)";
      PreparedStatement pStmt = conn.prepareStatement(sqlCmd);
      pStmt.setString(1, c.getName().trim());
      pStmt.setString(2, c.getDescription().trim());
      pStmt.execute();
      pStmt.close();

      c_id = getCollaborationId(c.getName().trim());
      for (String org : c.getCollaboratingOrganizations().getCollaboratingOrganization()) {
        int o_id = getOrganizationId(org.trim());
        if (o_id == -1) {
          throw new InvalidOrganizationNameException(org);
        }
        else {
          sqlCmd = "INSERT INTO tbl_COLLABORATING_ORGANIZATION " + "(c_id, o_id) VALUES (?,?)";
          pStmt = conn.prepareStatement(sqlCmd);
          pStmt.setInt(1, c_id);
          pStmt.setInt(2, o_id);
          pStmt.execute();
          pStmt.close();
        }
      }
      for (String type : c.getOutcomeTypes().getOutcomeType()) {
        sqlCmd = "INSERT INTO tbl_OUTCOME_TYPE " + "(c_id, outcome_type) VALUES (?,?)";
        pStmt = conn.prepareStatement(sqlCmd);
        pStmt.setInt(1, c_id);
        pStmt.setString(2, type.trim());
        pStmt.execute();
        pStmt.close();

      }
      for (String type : c.getCollaborationTypes().getCollaborationType()) {
        sqlCmd = "INSERT INTO tbl_COLLABORATION_TYPE " + "(c_id, c_type) VALUES (?,?)";
        pStmt = conn.prepareStatement(sqlCmd);
        pStmt.setInt(1, c_id);
        pStmt.setString(2, type.trim());
        pStmt.execute();
        pStmt.close();
      }
      for (BigInteger year : c.getYears().getYear()) {
        sqlCmd = "INSERT INTO tbl_YEAR " + "(c_id, y_type) VALUES (?,?)";
        pStmt = conn.prepareStatement(sqlCmd);
        pStmt.setInt(1, c_id);
        Date date = new Date(year.intValue(), 1, 1);
        pStmt.setDate(2, date);
        pStmt.execute();
        pStmt.close();
      }
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Gets the database ID of an collaboration given their name from the "tbl_COLLABORATION" table.
   * 
   * @param name Name of the collaboration.
   * @return a number.
   * @throws Exception if error occurs.
   */
  public int getCollaborationId(String name) throws Exception {
    int id = -1;
    PreparedStatement pStmt = null;
    ResultSet results = null;
    try {
      String sqlCmd = "SELECT c_id " + "FROM tbl_COLLABORATION " + "WHERE c_name = ?";
      pStmt = conn.prepareStatement(sqlCmd);
      pStmt.setString(1, name);
      results = pStmt.executeQuery();
      if (results.next()) {
        id = results.getInt("c_id");
      }
      else {
        id = -1;
      }
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
    finally {
      results.close();
      pStmt.close();
    }
    return id;
  }

  /**
   * Gets a list of all collaborations in the tbl_COLLABORATION table
   * 
   * @return an array list of collaboration objects.
   * @throws Exception if error occurs.
   */
  public ArrayList<Collaboration> getCollaborationList() throws Exception {
    ArrayList<Collaboration> list = new ArrayList<Collaboration>();
    Collaboration c;
    ResultSet results = null;
    try {
      String sqlCmd = "SELECT c_id, c_name, c_desc " + "FROM tbl_COLLABORATION";
      stmt = conn.createStatement();
      results = stmt.executeQuery(sqlCmd);
      while (results.next()) {
        c = new Collaboration();

        c.setName(results.getString("c_name"));
        c.setDescription(results.getString("c_desc"));
        // TODO getOutcomeType(int collabId)
        // TODO getYear(int collabId)
        // TODO getCollaborationType(int collabId)
        list.add(c);
      }
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
      list = null;
    }
    finally {
      results.close();
      stmt.close();
    }
    return list;

  }

  /**
   * Creates the tbl_COLLABORATION_TYPE Table.
   */
  public void createCollaborationTypeTable() {
    try {
      String sqlCmd = "CREATE TABLE tbl_COLLABORATION_TYPE ("
          + "   c_id          INT           NOT NULL"
          + "                 CONSTRAINT c_id_type_fk REFERENCES tbl_COLLABORATION,"
          + "   c_type        VARCHAR(128)  NOT NULL," +

          "   CONSTRAINT c_type_pk        PRIMARY KEY(c_id, c_type)" + ")";
      stmt = conn.createStatement();
      stmt.execute(sqlCmd);
      stmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Drops the tbl_COLLABORATION_TYPE Table.
   */
  public void dropCollaborationTypeTable() {
    try {
      String sqlCmd = "DROP TABLE tbl_COLLABORATION_TYPE";
      stmt = conn.createStatement();
      stmt.execute(sqlCmd);
      stmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Creates the tbl_OUTCOME_TYPE Table.
   */
  public void createOutcomeTypeTable() {
    try {
      String sqlCmd = "CREATE TABLE tbl_OUTCOME_TYPE ("
          + "   c_id                INT           NOT NULL"
          + "                                     CONSTRAINT c_id_fk"
          + "                                     REFERENCES tbl_COLLABORATION,"
          + "   outcome_type        VARCHAR(128)  NOT NULL," +

          "   CONSTRAINT outcome_pk          PRIMARY KEY(c_id, outcome_type)" + ")";
      stmt = conn.createStatement();
      stmt.execute(sqlCmd);
      stmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Drops the tbl_OUTCOME_TYPE Table.
   */
  public void dropOutcomeTypeTable() {
    try {
      String sqlCmd = "DROP TABLE tbl_OUTCOME_TYPE";
      stmt = conn.createStatement();
      stmt.execute(sqlCmd);
      stmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Creates the tbl_COLLABORATING_ORGANIZATION Table.
   */
  public void createCollaboratingOrganizationTable() {
    try {
      String sqlCmd = "CREATE TABLE tbl_COLLABORATING_ORGANIZATION ("
          + "   c_id          INT           NOT NULL"
          + "                               CONSTRAINT c_id_co_fk"
          + "                               REFERENCES tbl_COLLABORATION,"
          + "   o_id          INT           NOT NULL"
          + "                               CONSTRAINT o_id_co_fk"
          + "                               REFERENCES tbl_ORGANIZATION," +

          "   CONSTRAINT c_o_id_pk        PRIMARY KEY(c_id, o_id)" + ")";
      stmt = conn.createStatement();
      stmt.execute(sqlCmd);
      stmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Drops the tbl_COLLABORATING_ORGANIZATION Table.
   */
  public void dropCollaboratingOrganizationTable() {
    try {
      String sqlCmd = "DROP TABLE tbl_COLLABORATING_ORGANIZATION";
      stmt = conn.createStatement();
      stmt.execute(sqlCmd);
      stmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Adds an entry to the tbl_COLLABORATING_ORGANIZATION Table.
   * 
   * @param collabId Collaboration Id number.
   * @param orgId Organization Id number.
   */
  public void addCollaboratingOrganization(int collabId, int orgId) {
    try {
      String sqlCmd = "INSERT INTO tbl_COLLABORATING_ORGANIZATION " + "(c_id, o_id) "
          + "VALUES (?,?)";
      PreparedStatement pStmt = conn.prepareStatement(sqlCmd);
      pStmt.setInt(1, collabId);
      pStmt.setInt(2, orgId);
      pStmt.execute();
      pStmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Creates the tbl_COUNTRY table.
   */
  public void createCountryTable() {
    try {
      String sqlCmd = "CREATE TABLE tbl_COUNTRY ("
          + "   country_id          INT           GENERATED ALWAYS AS IDENTITY"
          + "                                     CONSTRAINT country_id_pk PRIMARY KEY,"
          + "   country_name        VARCHAR(64)   NOT NULL" + ")";
      stmt = conn.createStatement();
      stmt.execute(sqlCmd);
      stmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Drops the tbl_COUNTRY Table.
   */
  public void dropCountryTable() {
    try {
      String sqlCmd = "DROP TABLE tbl_COUNTRY";
      stmt = conn.createStatement();
      stmt.execute(sqlCmd);
      stmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Adds an country to the table "tbl_COUNTRY" in the database.
   * 
   * @param name name of country to add.
   */
  public void addCountry(String name) {
    try {
      String sqlCmd = "INSERT INTO tbl_COUNTRY " + "(country_name)" + "VALUES (?)";
      PreparedStatement pStmt = conn.prepareStatement(sqlCmd);
      pStmt.setString(1, name.trim());
      pStmt.execute();
      pStmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Gets the database ID of an country given the name from the "tbl_COUNTRY" table.
   * 
   * @param name Country name.
   * @return a number.
   * @throws Exception if error occurs.
   */
  public int getCountryId(String name) throws Exception {
    int id = -1;
    PreparedStatement pStmt = null;
    ResultSet results = null;
    try {
      String sqlCmd = "SELECT country_id " + "FROM tbl_COUNTRY " + "WHERE country_name = ?";
      pStmt = conn.prepareStatement(sqlCmd);
      pStmt.setString(1, name);
      results = pStmt.executeQuery();
      if (results.next()) {
        id = results.getInt("country_id");
      }
      else {
        id = -1;
      }
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
    finally {
      results.close();
      pStmt.close();
    }
    return id;
  }

  /**
   * Creates the tbl_YEAR Table.
   */
  public void createYearTable() {
    try {
      String sqlCmd = "CREATE TABLE tbl_YEAR (" + "   c_id          INT       NOT NULL"
          + "                 CONSTRAINT c_id_year_fk REFERENCES tbl_COLLABORATION,"
          + "   y_type        DATE      NOT NULL," +

          "   CONSTRAINT y_id_pk      PRIMARY KEY(c_id, y_type)" + ")";
      stmt = conn.createStatement();
      stmt.execute(sqlCmd);
      stmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Drops the tbl_YEAR Table.
   */
  public void dropYearTable() {
    try {
      String sqlCmd = "DROP TABLE tbl_YEAR";
      stmt = conn.createStatement();
      stmt.execute(sqlCmd);
      stmt.close();
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
    }
  }

  /**
   * Splits a full name into First,Middle,Last names
   * 
   * @param name Full name.
   * @return String[] of names.
   */
  public String[] splitUpName(String name) {
    try {
      String[] names = new String[3];
      int firstSpaceIndex = name.indexOf(' ');
      if (firstSpaceIndex == -1) {
        names[0] = name;
        names[1] = "";
        names[2] = "";
      }
      else {
        int lastSpaceIndex = name.lastIndexOf(' ');
        names[0] = name.substring(0, firstSpaceIndex).trim();
        names[1] = name.substring(firstSpaceIndex, lastSpaceIndex).trim();
        names[2] = name.substring(lastSpaceIndex, name.length()).trim();

      }
      return names;
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
      return null;
    }
  }

  /**
   * Combines a String[] list of names.
   * 
   * @param names String[] list.
   * @return Full name.
   */
  public String getFullName(String[] names) {
    try {
      String name = "";
      for (int i = 0; i < names.length; i++) {
        name = names[i] + " ";
      }
      name = name.trim();
      return name;
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
      return null;
    }
  }

  /**
   * Overload getFullName, combine set of strings into single name.
   * 
   * @param fName First Name.
   * @param mName Middle Name.
   * @param lName Last Name.
   * @return Full name.
   */
  public String getFullName(String fName, String mName, String lName) {
    try {
      String name = "";
      if (fName != " ") {
        name += fName;
      }
      if (mName != " ") {
        name += " " + mName.trim();
      }
      if (lName != " ") {
        name += " " + lName.trim();
      }
      return name;
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
      return null;
    }
  }

  /**
   * Gets the number of records in a table.
   * 
   * @param tableName Name of table.
   * @return the number of records or -1 if error occurs.
   * @throws SQLException if error occurs.
   */
  public int getTableCount(String tableName) throws SQLException {
    int count = -1;
    ResultSet results = null;
    try {
      String sqlCmd = "SELECT COUNT(*) " + "FROM " + tableName;
      // System.out.println("sqlCmd: " + sqlCmd);
      stmt = conn.createStatement();
      results = stmt.executeQuery(sqlCmd);
      results.next();
      // System.out.print(results.getInt(1));
      count = results.getInt(1);
    }
    catch (Exception e) {
      logger.log(Level.SEVERE, e.toString());
      return -1;
    }
    finally {
      results.close();
      stmt.close();
    }
    return count;
  }
}